package com.tsd.core.excel.impexcel;

import com.alibaba.fastjson.JSON;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.ArrayList;
import java.util.Map;
import java.util.Set;

public class ExcelReadUtils {
    public static Workbook getWorkbook(String excelFile) throws IOException {
        return getWorkbook(new FileInputStream(excelFile));
    }

    public static Workbook getWorkbook(InputStream is) throws IOException {
        Workbook wb;
        ByteArrayOutputStream byteOS = new ByteArrayOutputStream();
        byte[] buffer = new byte[512];
        int count = -1;
        while ((count = is.read(buffer)) != -1) {
            byteOS.write(buffer, 0, count);
        }
        byteOS.close();
        byte[] allBytes = byteOS.toByteArray();
        try {
            wb = new XSSFWorkbook(new ByteArrayInputStream(allBytes));
        } catch (Exception ex) {
            wb = new HSSFWorkbook(new ByteArrayInputStream(allBytes));
        }
        return wb;
    }

    public static FormulaEvaluator getFormulaEvaluator(Workbook wb) {
        FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
        return evaluator;
    }

    public static ArrayList<ArrayList<Object>> readAllRows(String excelFile) throws IOException {
        return readAllRows(new FileInputStream(excelFile));
    }

    public static ArrayList<ArrayList<Object>> readAllRows(String excelFile, Integer start) throws IOException {
        return readAllRows(new FileInputStream(excelFile), start);
    }

    public static ArrayList<ArrayList<Object>> readAllRows(InputStream is) throws IOException {
        Workbook wb = getWorkbook(is);
        FormulaEvaluator formulaEvaluator = getFormulaEvaluator(wb);
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();

        // 获取每个Sheet表
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            rowList.addAll(readRows(sheet, formulaEvaluator));
        }

        return rowList;
    }

    public static ArrayList<ArrayList<Object>> readAllRows(InputStream is, Integer start) throws IOException {
        Workbook wb = getWorkbook(is);
        FormulaEvaluator formulaEvaluator = getFormulaEvaluator(wb);
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();

        // 获取每个Sheet表
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            Sheet sheet = wb.getSheetAt(i);
            rowList.addAll(readRows(sheet, start, formulaEvaluator));
        }

        return rowList;
    }

    public static ArrayList<ArrayList<Object>> readRows(String excelFile, int startRowIndex)
            throws IOException {
        return readRows(new FileInputStream(excelFile), startRowIndex, 0);
    }

    public static ArrayList<ArrayList<Object>> readRows(String excelFile, int startRowIndex, int rowCount)
            throws IOException {
        return readRows(new FileInputStream(excelFile), startRowIndex, rowCount, 0);
    }

    public static ArrayList<ArrayList<Object>> readRows(String excelFile, int startRowIndex, int rowCount, int sheetIndex)
            throws IOException {
        return readRows(new FileInputStream(excelFile), startRowIndex, rowCount, sheetIndex);
    }

    public static ArrayList<ArrayList<Object>> readRows(String excelFile) throws IOException {
        return readRows(new FileInputStream(excelFile));
    }

    public static ArrayList<ArrayList<Object>> readRows(InputStream is, int startRowIndex, int sheetIndex)
            throws IOException {
        Workbook wb = getWorkbook(is);
        Sheet sheet = wb.getSheetAt(sheetIndex);
        int rowCount = sheet.getLastRowNum() + 1;
        FormulaEvaluator formulaEvaluator = getFormulaEvaluator(wb);
        return readRows(sheet, startRowIndex, rowCount, formulaEvaluator);
    }

    public static ArrayList<ArrayList<Object>> readRows(InputStream is, int startRowIndex, int rowCount, int sheetIndex)
            throws IOException {
        Workbook wb = getWorkbook(is);
        Sheet sheet = wb.getSheetAt(sheetIndex);
        FormulaEvaluator formulaEvaluator = getFormulaEvaluator(wb);
        return readRows(sheet, startRowIndex, rowCount, formulaEvaluator);
    }

    public static ArrayList<ArrayList<Object>> readRows(InputStream is) throws IOException {
        Workbook wb = getWorkbook(is);
        Sheet sheet = wb.getSheetAt(0);
        FormulaEvaluator formulaEvaluator = getFormulaEvaluator(wb);
        return readRows(sheet, formulaEvaluator);
    }

    public static ArrayList<ArrayList<Object>> readRows(Sheet sheet, int startRowIndex, int rowCount, FormulaEvaluator formulaEvaluator) {
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();

        for (int i = startRowIndex; i < (startRowIndex + rowCount); i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                break;
            }
            ArrayList<Object> cellList = new ArrayList<Object>();
			/*for (Cell cell : row) {
				cellList.add(readCell(cell));
			}*/
            for (int j = 0; j < row.getPhysicalNumberOfCells(); j++) {
                cellList.add(readCell(row.getCell(j), formulaEvaluator));
            }
            rowList.add(cellList);
        }
        return rowList;
    }

    public static ArrayList<ArrayList<Object>> readRows(Sheet sheet, FormulaEvaluator formulaEvaluator) {
        int rowCount = sheet.getPhysicalNumberOfRows();
        return readRows(sheet, 0, rowCount, formulaEvaluator);
    }

    public static ArrayList<ArrayList<Object>> readRows(Sheet sheet, Integer start, FormulaEvaluator formulaEvaluator) {
        int rowCount = sheet.getPhysicalNumberOfRows() - start;
        return readRows(sheet, start, rowCount, formulaEvaluator);
    }

    /**
     * 从Excel读Cell
     *
     * @param cell
     * @return
     */
    private static Object readCell(Cell cell, FormulaEvaluator formulaEvaluator) {
        if (cell == null) {
            return null;
        }
        if (CellType.STRING == cell.getCellType()) {
            String str = cell.getRichStringCellValue().getString();
            return str == null ? "" : str.trim();
        }
        if (CellType.NUMERIC == cell.getCellType()) {
            if (DateUtil.isCellDateFormatted(cell)) {
                return cell.getDateCellValue();
            } else {
                cell.setCellType(CellType.STRING);
                return cell.getStringCellValue();
            }
        }
        if (CellType.BOOLEAN == cell.getCellType()) {
            return cell.getBooleanCellValue();
        }
        if (CellType.FORMULA == cell.getCellType()) {
            return getCellValue(formulaEvaluator.evaluate(cell));
        }
        if (CellType.BLANK == cell.getCellType()) {
            return "";
        }
        System.err.println("Data error for cell of excel: " + cell.getCellType());
        return "";
    }

    private static String getCellValue(CellValue cell) {
        String cellValue = null;
        if (CellType.STRING == cell.getCellType()) {
            System.out.print("String :");
            cellValue = cell.getStringValue();
        } else if (CellType.NUMERIC == cell.getCellType()) {
            System.out.print("NUMERIC:");
            cellValue = String.valueOf(cell.getNumberValue());
        } else if (CellType.FORMULA == cell.getCellType()) {
            System.out.print("FORMULA:");
        }
        return cellValue;
    }

    public static void updateExcelFile(String file, Map<Integer, String> map, Integer start) throws Exception {
        InputStream is = new FileInputStream(file);
        Workbook wb = getWorkbook(is);
        int size = wb.getSheetAt(0).getPhysicalNumberOfRows();
        Row row = null;
        Set<Integer> set = map.keySet();
        for (Integer errorRowIndex : set) {
            if (errorRowIndex + start < size) {
                row = wb.getSheetAt(0).getRow(errorRowIndex + start);
            } else {
                Integer a = errorRowIndex % size;
                int b = (errorRowIndex - a) / size;
                row = wb.getSheetAt(b).getRow(a + start);
            }
            int max = row.getPhysicalNumberOfCells();
            Cell cell = row.createCell(max);
            cell.setCellValue(map.get(errorRowIndex));
        }

        OutputStream os = new FileOutputStream(file);
        wb.write(os);
        os.flush();
    }

    public static void main(String[] args) {
        String fileName = "D:/temp/JHK-YJ1标段工程量固化清单（20180328）.xls";
        try {
            Workbook wk = getWorkbook(fileName);
            int count = wk.getNumberOfSheets();
            FormulaEvaluator formulaEvaluator = getFormulaEvaluator(wk);
            for (int i = 0; i < count; i++) {
                ArrayList<ArrayList<Object>> list = readRows(wk.getSheetAt(i), formulaEvaluator);
                System.out.println(JSON.toJSONString(list));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
